package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.AttackedAnalysis;
import com.youan.log.modules.audit.service.IAttackedAnalysisService;
import org.apache.commons.lang.text.StrBuilder;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 被攻击者攻击行为分析
 * @author xsh
 * @return
 */
public class AttackedAnalysisDataExtract{

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private IP2Region ip2Region;

    private String tmpTableName;
    private JdbcTemplate jdbcTemplate;
    private IAttackedAnalysisService analysisService;

    private List<AttackedAnalysis> waitPusToDB;

    public AttackedAnalysisDataExtract(String date, ThreadTaskProgressor taskProgressor, IP2Region ip2Region) {
        this.date = date;
        this.taskProgressor = taskProgressor;
        this.ip2Region = ip2Region;

        this.tmpTableName = "tmp_attackded_ip_" + date.replaceAll("-", "_");
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.analysisService = SpringUtil.getBean(IAttackedAnalysisService.class);

        this.waitPusToDB = new ArrayList<>(5000);
    }

    public void extract() {
        this.taskProgressor.setProgressCommitSize(1);
        this.taskProgressor.updateProgressTotal(this.taskProgressor.getProgressTotalNum() + 4);

        // 创建临时表
        createTmpTable();
        this.taskProgressor.addProgressNum();
        // 向临时表生成数据
        generateData2TmpTable();
        this.taskProgressor.addProgressNum();
        // 向最终表中生成数据
        generateFinalData();
        this.taskProgressor.addProgressNum();
        // 删除临时表
        dropTmpTable();
        this.taskProgressor.addProgressNum();
    }

    private void dropTmpTable() {
        String sql = "drop table " + this.tmpTableName;
        this.jdbcTemplate.execute(sql);
    }

    private void generateFinalData() {
        String sql = "select * from "+this.tmpTableName;
        this.jdbcTemplate.query(sql, rs -> {
            try {
                AttackedAnalysis analysis = convert2AttackedBean(rs);
                waitPusToDB.add(analysis);
            }catch (SQLException e) {
                return;
            }
            if(waitPusToDB.size() >= 5000) {
                this.analysisService.saveBatch(waitPusToDB);
                waitPusToDB = new ArrayList<>(5000);
            }
        });

        this.analysisService.saveBatch(waitPusToDB);

        // 批量更新下总数，总数是根据所有与被攻击者有关的所有攻击表IP的和
        String updateSql = "UPDATE log_attacked_analysis a\n" +
                "SET a.attacked_total_count = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(visit_number)\n" +
                "  FROM\n" +
                "   log_statistical\n" +
                "  WHERE\n" +
                "   visit_date = '" + this.date + "'\n" +
                "  AND source_ip = a.attacked_ip\n" +
                "  AND dest_ip IN (\n" +
                "   SELECT\n" +
                "    attacker_ip\n" +
                "   FROM\n" +
                "    log_attack_analysis\n" +
                "  )\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_total_count\n" +
                "WHERE date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        updateSql = "UPDATE log_attacked_analysis a\n" +
                "SET a.attacked_total_count = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(visit_number)\n" +
                "  FROM\n" +
                "   log_statistical\n" +
                "  WHERE\n" +
                "   visit_date = '" + this.date + "'\n" +
                "  AND dest_ip = a.attacked_ip\n" +
                "  AND source_ip IN (\n" +
                "   SELECT\n" +
                "    attacker_ip\n" +
                "   FROM\n" +
                "    log_attack_analysis\n" +
                "  )\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_total_count\n" +
                "WHERE date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        // 批量更新攻击成功的，攻击成功等于总攻击数-登录失败
        updateSql = "UPDATE log_attacked_analysis a\n" +
                "SET a.attacked_success_count = a.attacked_total_count - a.login_failure_count\n" +
                "WHERE date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);
    }

    private AttackedAnalysis convert2AttackedBean(ResultSet rs) throws SQLException {
        String attackedIp = rs.getString("attacked_ip");
        int loginSuccessCount = rs.getInt("login_success_count");
        int loginFailureCount = rs.getInt("login_failure_count");
        int attackFunctionCount1 = rs.getInt("attack_function_count1");
        int attackFunctionCount2 = rs.getInt("attack_function_count2");
        IP2Region.Region region = ip2Region.ip2Region(attackedIp);

        AttackedAnalysis result = new AttackedAnalysis();
        result.setDate(date);
        result.setAttackedIp(attackedIp);
        result.setAttackedTotalCount(0); // 设置为0方便之后更新直接+
        result.setAttackedSuccessCount(0);// 设置为0方便之后更新直接+
        result.setLoginSuccessCount(loginSuccessCount);
        result.setLoginFailureCount(loginFailureCount);
        result.setAttackFunctionCount(attackFunctionCount1 + attackFunctionCount2);
        result.setCountry(region.getCountry());
        result.setProvince(region.getProvince());
        result.setCity(region.getCity());
        result.setIsp(region.getIsp());

        return result;
    }

    private void generateData2TmpTable() {
        generateAttackedIpColumnData();
        generateLoginSuccessCountColumnData();
        generateLoginFailureCountColumnData();
        generateAttackFunctionCount1ColumnData();
        generateAttackFunctionCount2ColumnData();
    }

    /**
     * 生成attack_function_count2字段数据
     * UPDATE tmp_attackded_ip_2021_03_14 t1
     * SET t1.attack_function_count2 = IFNULL(
     * (
     * SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.dest_ip AND t2.source_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * AND t2.visit_date = '2021-03-14' AND t2.issue_type in ('10050002295', '10050002065', '10050002066') GROUP BY t1.attacked_ip
     * ),0)
     */
    private void generateAttackFunctionCount2ColumnData() {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(this.tmpTableName).append(" t1")
                .append(" SET t1.attack_function_count2 = IFNULL(")
                .append(" (")
                .append(" SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.dest_ip AND t2.source_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append(" AND t2.visit_date = '").append(this.date).append("' AND t2.issue_type in ('10050002295', '10050002065', '10050002066') GROUP BY t1.attacked_ip")
                .append(" ),0)");
        this.jdbcTemplate.execute(sql.toString());
    }

    /**
     * 生成attack_function_count1字段数据
     * UPDATE tmp_attackded_ip_2021_03_14 t1
     * SET t1.attack_function_count1 = IFNULL(
     * (
     * SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * AND t2.visit_date = '2021-03-14' AND issue_type in ('10050001948') GROUP BY t1.attacked_ip
     * ),0)
     */
    private void generateAttackFunctionCount1ColumnData() {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(this.tmpTableName).append(" t1")
                .append(" SET t1.attack_function_count1 = IFNULL(")
                .append(" (")
                .append(" SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append(" AND t2.visit_date = '").append(this.date).append("' AND issue_type in ('10050001948') GROUP BY t1.attacked_ip")
                .append("),0)");
        this.jdbcTemplate.execute(sql.toString());
    }

    /**
     * 生成login_failure_count列数据
     * UPDATE tmp_attackded_ip_2021_03_14 t1
     * set t1.login_failure_count = IFNULL(
     * (
     * SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * AND t2.visit_date = '2021-03-14' AND t2.issue_type = '10050001084' GROUP BY t1.attacked_ip
     * ),0)
     */
    private void generateLoginFailureCountColumnData() {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(this.tmpTableName).append(" t1")
                .append(" set t1.login_failure_count = IFNULL(")
                .append(" (")
                .append(" SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append("AND t2.visit_date = '").append(this.date).append("' AND t2.issue_type = '10050001084' GROUP BY t1.attacked_ip")
                .append("),0)");
        this.jdbcTemplate.execute(sql.toString());
    }

    /**
     * 生成login_success_count列数据
     * UPDATE tmp_attackded_ip_2021_03_14 t1
     * SET t1.login_success_count = IFNULL(
     * (
     * SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE
     * 	t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * 	AND t2.visit_date = '2021-03-14' AND t2.issue_type = '10050001476' GROUP BY t1.attacked_ip
     * ),0);
     */
    private void generateLoginSuccessCountColumnData() {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(this.tmpTableName).append(" t1")
                .append(" SET t1.login_success_count = IFNULL(")
                .append(" (")
                .append(" SELECT SUM(t2.visit_number) FROM log_statistical t2 WHERE ")
                .append(" t1.attacked_ip = t2.source_ip AND t2.dest_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append(" AND t2.visit_date = '").append(this.date).append("' AND t2.issue_type = '10050001476' GROUP BY t1.attacked_ip")
                .append(" )")
                .append(",0)");
        this.jdbcTemplate.execute(sql.toString());
    }

    /**
     * 生成attacked_ip列的数据
     * INSERT INTO attackded_ip_2020_12_19 (attacked_ip)
     * SELECT source_ip FROM log_statistical WHERE visit_date = '2020-12-19'
     * AND issue_type = '10050001476' AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * UNION
     * SELECT source_ip FROM log_statistical WHERE visit_date = '2020-12-19'
     * AND issue_type = '10050001084' AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis)
     * UNION
     * SELECT source_ip FROM log_statistical WHERE visit_date = '2020-12-19'
     * AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis) AND issue_type in ('10050001948')
     * UNION
     * SELECT dest_ip FROM log_statistical WHERE visit_date = '2020-12-19'
     * AND source_ip in (SELECT attacker_ip FROM log_attack_analysis) AND issue_type in ('10050002295', '10050002065', '10050002066')
     */
    private void generateAttackedIpColumnData() {
        StrBuilder sql = new StrBuilder();
        sql.append("INSERT INTO ").append(this.tmpTableName).append(" (attacked_ip)")
                .append(" SELECT source_ip FROM log_statistical WHERE visit_date = '").append(this.date).append("'")
                    .append(" AND issue_type = '10050001476' AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append(" UNION")
                .append(" SELECT source_ip FROM log_statistical WHERE visit_date = '").append(this.date).append("'")
                    .append(" AND issue_type = '10050001084' AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis)")
                .append(" UNION")
                .append(" SELECT source_ip FROM log_statistical WHERE visit_date = '").append(this.date).append("'")
                    .append(" AND dest_ip in (SELECT attacker_ip FROM log_attack_analysis) AND issue_type in ('10050001948')")
                .append(" UNION")
                .append(" SELECT dest_ip FROM log_statistical WHERE visit_date = '").append(this.date).append("'")
                    .append(" AND source_ip in (SELECT attacker_ip FROM log_attack_analysis) AND issue_type in ('10050002295', '10050002065', '10050002066')");
        this.jdbcTemplate.execute(sql.toString());
    }

    /**
     * 创建临时表
     * CREATE TABLE `attackded_ip_2020_12_19` (
     *   `attacked_ip` varchar(255),
     *   `login_success_count` int DEFAULT 0,
     *   `login_failure_count` int DEFAULT 0,
     *   `attack_function_count1` int DEFAULT 0,
     * 	`attack_function_count2` int DEFAULT 0
     * ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
     */
    private void createTmpTable() {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE `").append(this.tmpTableName).append("` (")
                .append(" `attacked_ip` varchar(255),")
                .append(" `login_success_count` int DEFAULT 0,")
                .append(" `login_failure_count` int DEFAULT 0,")
                .append(" `attack_function_count1` int DEFAULT 0,")
                .append(" `attack_function_count2` int DEFAULT 0")
                .append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci");
        this.jdbcTemplate.execute(sql.toString());
    }
}
